<?php
/**
 * 从给定的数据表中，把数据导入库里
 * @author ray
 */
apf_require_class('Oa_Job_Abstract');
apf_require_class("Const_BaseInfo");
class Oa_Job_Hr_InsertData extends Oa_Job_Abstract {
    private $read_db = "hroa_slave";//读数据库
    private $write_db = "hroa_master";//写数据库
    private $i_start_time = 0;//开始日期（updated）
    private $i_end_time = 0;//结束日期（updated）
    private $file;//文件指针

   function __construct(){
        $this->i_start_job_time = microtime(true);
        $this->log('start job work');
    }

    public function run($flag = -1) {
        switch ($flag){
            case 1://导入基础信息，城市，事业部，部门类型
                $this->_insert_base_info();
                break;
            case 2://更新岗位类型表(positon表)
                $this->insert_positon();
                break;
            case 3://更新职能类型和岗位类型关系(function_position表)
                $this->insert_function_position();
                break;
            case 4://更新部门表(department表)
                $this->insert_department();
                break;
            case 5://更新职位表(JOB表)
                $this->insert_job();
                break;
            //case 5://更新员工user_id(user表)
            //    $this->update_user_info();
            //    break;
            case 6://更新职位表(work表)
                $this->insert_work();
                break;
            case 7://更新上下级关系(relation表)
                $this->update_relation_info();
                break;
            case 8://更新职位表(job表)
                    $this->update_job_fact();
                    break;
            case 9://更新职位表(department表)
                    $this->update_department_fact();
                    break;
            case 10:
                    $this->update_user_info();
                    break;
            case 'f1':
                    //更新合同期限
                    $this->update_join_date();
                    break;
            case 'f2':
                   //更新合同年限
                   $this->update_join_keep();
                   break;
           case 11://20130128 技术部改成研发中心
               $this->insert_depart_function();
               break;
            default://依次更新全部表
                //$this->insert_positon();
                //$this->insert_function_position();
                //$this->insert_department();
                // $this->insert_job();
                //$this->insert_work();
                //$this->update_job_fact();
                //$this->update_department_fact();
                break;
        }
    }
    private function insert_depart_function(){
        $sql = 'SELECT * FROM function_position  WHERE function_id = 6 ';
        $re = $this->get_db_rows($sql,$this->write_db);
        $sql = 'INSERT INTO function_position (function_id, position_id, created) VALUES ';
        $time=time();
        for($i=13;$i<=16;$i++){
            foreach($re as $val){
                $sql .= '('.$i.','.$val['position_id'].','.$time.'),';
            }
        }
        $sql = substr($sql, 0, -1);
        $re = $this->execute($sql,array(),$this->write_db);
    }
    //更新合同年限
    private function update_join_keep(){
        $ssql = "SELECT user_code, join_start, join_end, join_keep FROM user WHERE join_start !='' AND join_end != ''";
        $infos = $this->get_db_rows($ssql,$this->write_db);
        foreach($infos as $temp){
            $user_code = trim($temp['user_code']);
            if($temp['join_keep'] < 1 && $temp['join_keep'] > 0){
                $error[] = "工号 $user_code 的合同年限({$temp['join_keep']})未做更改！";
                continue;
            }

            $join_start_stamp = strtotime($temp['join_start']);
            $join_end_stamp = strtotime($temp['join_end']);
            if($join_start_stamp && $join_end_stamp){
                $join_keep = number_format(($join_end_stamp - $join_start_stamp) /(365*24*3600),2);
                $sql = "UPDATE user SET join_keep = {$join_keep} WHERE user_code = '{$user_code}'";
                $this->execute($sql,array(),$this->write_db);
            }else{
                $error[] = "工号 $user_code 的日期格式错误！";
            }
        }
        if($error){
            $this->log(print_r($error,true));
        }
        $this->log('Update join_keep successfully!');
    }
    //更新紧急联系人
    private function update_join_date(){
        $file = dirname(__FILE__).'/data/update_date20121206.csv';

        if (!file_exists($file)) {
            $this->log($file."不存在,请输入正确的问题文件");exit;
        }
        $error = array();
        $this->file = fopen($file,"r");

        while($temp = fgetcsv($this->file)){
            $user_code = trim($temp[0]);
            $join_start_stamp = strtotime($temp[1]);
            $join_end_stamp = strtotime($temp[2]);
            if($join_start_stamp && $join_end_stamp){
                $join_start = date('Ymd',$join_start_stamp);
                $join_end = date('Ymd',$join_end_stamp);
                $sql = "UPDATE user SET join_start = {$join_start}, join_end = {$join_end} WHERE user_code = '{$user_code}'";
                $this->execute($sql,array(),$this->write_db);
            }else{
                $error[] = "工号 $user_code 的日期格式错误！";
            }
        }
        if($error){
            $this->log(print_r($error,true));
        }
        $this->log('Update join_date successfully!');
    }
    //更新上下级关系(relation表)
    private function update_relation_info(){
        $sql = 'SELECT user_id, job_id FROM work';
        $info = $this->get_db_rows($sql,$this->write_db);
        $error = array();
        foreach($info as $val){
            $isql = "SELECT superior_id parent_id FROM `attendance`.`org_relation` WHERE `level` = 1 AND user_id = {$val['user_id']};";
            $pre = $this->get_db_row($isql,$this->write_db);//查出上级ID
            if($pre["parent_id"]){
                $sql = "SELECT job_id FROM work WHERE user_id = {$pre["parent_id"]}";
                $jre = $this->get_db_row($sql,$this->write_db);//查出上级ID
                if($jre['job_id']){
                    $check_sql = "SELECT relation_id FROM relation WHERE user_id={$val['user_id']} AND user_job_id={$val['job_id']} AND parent_id={$pre['parent_id']} AND parent_job_id={$jre['job_id']};";
                    $cre = $this->get_db_row($check_sql,$this->write_db);//查询是否已存在
                    if(!$cre['relation_id']){
                        $iisql = "INSERT INTO relation (user_id,user_job_id,parent_id,parent_job_id) VALUES ({$val['user_id']},{$val['job_id']},{$pre['parent_id']},{$jre['job_id']});";
                        $this->execute($iisql,array(),$this->write_db);
                    }else{
                        $error[] = "已经存在的关系：user_id={$val['user_id']} AND user_job_id={$val['job_id']} AND parent_id={$pre['parent_id']} AND parent_job_id={$jre['job_id']}";
                    }

                }
                else{
                    $error[] ="{$val['user_id']} his no job!";
                }
            }else{
                $error[] ="{$val['user_id']} his no parent!";
            }
        }
        if(empty($error)){
            $this->log('Insert relation successfully!');
        }else{
            $this->log(print_r($error,true));
        }
    }
    //更新紧急联系人
    private function update_user_info(){
        $file = dirname(__FILE__).'/data/user.csv';

        if (!file_exists($file)) {
            $this->log($file."不存在,请输入正确的问题文件");exit;
        }
        $error = array();
        $this->file = fopen($file,"r");
        $time = time();
        while($temp = fgetcsv($this->file)){
            $user_code = trim($temp[0]);
            $emergency_people = trim($temp[1])?trim($temp[1]):'';
            $emergency_phone = trim($temp[2])?trim($temp[2]):'';
            $join_keep=0;
            $join_start_sql = '';
            $join_end_sql = '';
            $temp[3] = trim($temp[3]);
            $temp[4] = trim($temp[4]);
            $temp[5] = trim($temp[5]);
            if(!empty($temp[3]) && !empty($temp[4])){
                $join_start = date('Ymd',strtotime($temp[3]));
                $join_end = date('Ymd',strtotime($temp[4]));
                $join_end_sql = ",join_end = {$join_end}";
                $join_start_sql = ",join_start = {$join_start}";
                $tmp_join_end = strtotime($temp[4])+88400;
                $join_keep  = date('Y',$tmp_join_end) - date('Y',strtotime($temp[3]));
            }

            $indue_date = 0;
            if(!empty($temp[5])){
                $indue_date = date('Ymd',strtotime($temp[5]));
            }


            //$join_keep = ($join_keep < 3 )? 3 : $join_keep;
            $times = intval($temp[6]);
            //插入user表基本信息
                $sql = "UPDATE user SET emergency_people = '{$emergency_people}' $join_start_sql $join_end_sql,join_keep = {$join_keep},indue_date = {$indue_date} , emergency_phone = '{$emergency_phone}' WHERE user_code = '{$user_code}'";
                $re = $this->execute($sql,array(),$this->write_db);
                if(!$re){
                    $error[] = $user_code;
                }
            //插入join_log表
            if($times > 1){
                $sql = "SELECT user_id FROM user WHERE user_code = '$user_code'";
                $re = $this->get_db_row($sql, $this->write_db);
                if($re['user_id']){
                    $c_sql = "SELECT user_id FROM join_log WHERE user_id = {$re['user_id']}";
                    $cre = $this->get_db_row($c_sql, $this->write_db);
                    if($cre['user_id']){
                        continue;
                    }
                    $join_log_sql = "INSERT INTO join_log (user_id,join_start,join_end,join_keep) VALUES({$re['user_id']},$join_start,$join_end,$join_keep)";
                    for($i=0;$i<$times-1;$i++){
                        $this->execute($join_log_sql,array(),$this->write_db);
                        usleep(10000);
                    }
                }else{
                    $this->log('导入合同日志出错，不存在的user_code:'.$user_code);
                }

            }

        }
        if($error){
            $this->log('为更新成功工号：'.print_r($error,true));
        }
        $this->log('Update user_info successfully!');
    }
    //更新DEPARTMENT表实际人数信息
    private function update_department_fact(){
        $sql = 'SELECT department_id,SUM(fact_num) total FROM job WHERE is_deleted = 1 GROUP BY department_id';
        $re = $this->get_db_rows($sql,$this->write_db);

        $sal = 'UPDATE department SET fact_num = 0 WHERE is_deleted = 1';//初始化
        $this->execute($sql,array(),$this->write_db);

        foreach($re as $row){
            if(!$row['total']){
                continue;
            }
            $sql = "UPDATE department SET fact_num = {$row['total']} WHERE department_id = {$row['department_id']}";
            $ure = $this->execute($sql,array(),$this->write_db);
            if(!$ure){
                $this->log('Update department_fact no change!!!!!!!!!!!!!!!!!!!!');
            }
        }
        $this->log('Update department_fact successfully!');
    }
    //更新JOB表实际人数信息
    private function update_job_fact(){
        $sql = 'SELECT job_id,COUNT(*) total FROM work  WHERE is_deleted = 1 GROUP BY job_id';
        $re = $this->get_db_rows($sql,$this->write_db);

        $sql = 'UPDATE job SET fact_num = 0 WHERE is_deleted = 1';//初始化
        $this->execute($sql,array(),$this->write_db);

        foreach($re as $row){
            if(!$row['total']){
                continue;
            }
            $sql = "UPDATE job SET fact_num = {$row['total']} WHERE job_id = {$row['job_id']}";
            $ure = $this->execute($sql,array(),$this->write_db);
            if(!$ure){
                $this->log('Update job_fact no change!!!!!!!!!!!!!!!!!!!!');
            }
        }
        $this->log('Update job_fact successfully!');
    }
    //任职信息（WORK表）
    private function insert_work(){
        $file = dirname(__FILE__).'/data/work.csv';
        if (!file_exists($file)) {
            $this->log($file."不存在,请输入正确的问题文件");exit;
        }
        $error = array();
        $in_sql = 'INSERT INTO work (job_id,user_id,created) VALUES ';
        $this->file = fopen($file,"r");
        $time = time();
        while($temp = fgetcsv($this->file)){
            $city_name = trim($temp[0]);
            $bu_name = trim($temp[1]);
            $department_name = trim($temp[2]);
            $position_name = trim($temp[3]);
            $user_code = trim($temp[4]);
            $sql = "SELECT d.job_id job_id FROM business_unit a,city b,department c,job d WHERE c.city_id=b.city_id AND c.bu_id = a.bu_id AND d.department_id = c.department_id AND b.city_name = '{$city_name}' AND a.bu_name = '{$bu_name}' AND c.department_name = '{$department_name}' AND d.position_name = '{$position_name}'";
            //$sql = "SELECT job_id FROM v_work WHERE city_name = '{$city_name}' AND bu_name = '{$bu_name}' AND department_name = '{$department_name}' AND position_name = '{$position_name}'";
            $re = $this->get_db_row($sql,$this->write_db);
            if(isset($re['job_id'])){
                $job_id = $re['job_id'];
                $sql = "SELECT user_id FROM user WHERE user_code = '$user_code'";
                $re = $this->get_db_row($sql,$this->write_db);
                if(isset($re['user_id'])){
                    $user_id = $re['user_id'];
                    $sql = "SELECT work_id FROM work WHERE user_id = $user_id AND job_id = $job_id";
                    $re = $this->get_db_row($sql,$this->write_db);
                    if(!$re['work_id']){
                        $sql = $in_sql."($job_id, $user_id, $time)";
                        $re = $this->execute($sql,array(),$this->write_db);
                        if(!$re){
                            $this->log('Insert work failed:'.$sql);
                        }
                    }else{
                        $this->log( "已存在的任职信息：user_id = $user_id AND job_id = $job_id" );
                        continue;
                    }
                }else{
                    $error[] = '不存在的工号：'.$user_code;
                }
            }else{
                $error[] =  "不存在的职位信息：city_name = '{$city_name}' AND bu_name = '{$bu_name}' AND department_name = '{$department_name}' AND position_name = '{$position_name}'";
            }
        }
        if(!empty($error)){
            $this->log(print_r($error,true));
        }
        $this->log('Insert work successfully!');
    }
    //职位信息（JOB表）
    private function insert_job(){
        $file = dirname(__FILE__).'/data/function_position.csv';
        if (!file_exists($file)) {
            $this->log($file."不存在,请输入正确的问题文件");exit;
        }
        //获取职能数据
        $function_temp = $this->_get_function_list();

        //获取职位信息
        $position_temp = $this->_get_position_list();

        $sql = 'INSERT INTO job (position_id,position_name,department_id,created) VALUES ';
        //数据量小，一口气读入所有职位
        $this->file = fopen($file,"r");
        $a_info = array();
        $time = time();
        while($temp = fgetcsv($this->file)){
            $key = trim($temp[0]);
            $val = trim($temp[1]);
            if(in_array($key, $function_temp)){
                if(in_array($val, $position_temp)){

                    $p = array_keys($position_temp, $val);
                    if(count($p)>1){
                        echo '相同的职位类型名称： '.$val;
                        die;
                    }

                    $f = array_keys($function_temp, $key);
                    if(count($f)>1){
                        echo '相同的部门名称： '.$key;
                        die;
                    }
                    //查找该职能属性的部门ID
                    $f_sql = "SELECT department_id FROM department WHERE function_id = {$f[0]}";
                    $a_function_ids = $this->get_db_rows($f_sql, $this->write_db);
                    if(!$a_function_ids){
                        echo 'department表中不存在职能类型： '.$key;
                        die;
                    }
                    foreach($a_function_ids as $function_row){
                        $ssql = "SELECT job_id FROM job WHERE position_id = {$p[0]} AND department_id = {$function_row['department_id']}";
                        $sre = $this->get_db_row($ssql, $this->write_db);
                        if(!$sre['job_id']){
                            $sql .= "({$p[0]}, '{$val}', {$function_row['department_id']}, {$time}),";
                        }else{
                            $error[] = "已存在的job信息：position_id = {$p[0]} AND department_id = {$function_row['department_id']}";
                        }

                    }

                }else{
                    echo '不存在的职位类型： '.$val;
                    die;
                }
            }
            else{
                echo '不存在的部门名称： '.$key;
                die;
            }
        }
        $sql = substr($sql, 0, -1);

        $re = $this->execute($sql,array(),$this->write_db);
        if($re){
            $this->log('Insert job successfully!');
        }else{
            $this->log('Insert job failed!!!!!!!!!!!!!!!!!!!!');
        }
    }
    //插入职位数据
    private function insert_department(){
        $file = dirname(__FILE__).'/data/department.csv';
        if (!file_exists($file)) {
            $this->log($file."不存在,请输入正确的问题文件");exit;
        }
        //获取职能数据
        $function_temp = $this->_get_function_list();
        //获取城市数据
        $city_temp = $this->_get_city_list();
        //获取岗位类型
        //$position_temp = $this->_get_position_list();
        //获取事业部
        $business_temp = $this->_get_business_list();

        //数据量小，一口气读入所有职位
        $this->file = fopen($file,"r");
        $a_info = array();
        $a_check = array();
        while($temp = fgetcsv($this->file)){
            $city_id = array_pop(array_keys($city_temp, trim($temp[0])));
            if(!$city_id){
                echo '不存在城市：'.trim($temp[0]);exit;
            }
            $business_id = array_pop(array_keys($business_temp, trim($temp[1])));
            if(!$business_id){
                echo '事业部：'.trim($temp[1]);exit;
            }
            $function_id = array_pop(array_keys($function_temp, trim($temp[2])));
            if(!$function_id){
                echo '职能部门：'.trim($temp[2]);exit;
            }
            $cost_city_id = array_pop(array_keys($city_temp, trim($temp[3])));
            if(!$cost_city_id){
                echo '不存在城市：'.trim($temp[3]);exit;
            }
            //检测重复
            $check_str = "$city_id|$cost_city_id|$business_id|$function_id";
            if(in_array($check_str, $a_check)){
                $this->log("存在重复的数据:".print_r($temp));exit;
            }
            $a_info[] = array(
                    'city_id'=>$city_id,
                    'cost_city'=>$cost_city_id,
                    'bu_id'=>$business_id,
                    'function_id'=>$function_id,
                    'department_name'=>trim($temp[2])
            );
            $a_check[] = $check_str;
        }
        //一次性插入数据
        if($a_info){
            $sql = 'INSERT INTO department (department_name, function_id, city_id, bu_id, company_id, cost_city, created) VALUES ';
            $vals = '';
            $time = time();
            foreach($a_info as $row){
                $vals .= "('{$row['department_name']}',{$row['function_id']} ,{$row['city_id']} ,{$row['bu_id']}, 1, {$row['cost_city']}, $time),";
            }
            $sql = substr($sql.$vals, 0, -1);
        }else{
            $this->log("存在重复的岗位类型:'$temp'");exit;
        }
        $re = $this->execute($sql,array(),$this->write_db);
        if($re){
            $this->log('Insert department successfully!');
        }else{
            $this->log('Insert department failed!!!!!!!!!!!!!!!!!!!!');
        }
    }
    //插入职能和职位关系
    private function insert_function_position(){
        $file = dirname(__FILE__).'/data/function_position.csv';
        if (!file_exists($file)) {
            $this->log($file."不存在,请输入正确的问题文件");exit;
        }
        //获取职能数据
        $function_temp = $this->_get_function_list();

        //获取职位信息
        $position_temp = $this->_get_position_list();

        $sql = 'INSERT INTO function_position (function_id, position_id, created) VALUES ';
        //数据量小，一口气读入所有职位
        $this->file = fopen($file,"r");
        $a_info = array();
        $time = time();
        while($temp = fgetcsv($this->file)){
                $key = trim($temp[0]);
                $val = trim($temp[1]);
                if(in_array($key, $function_temp)){
                    if(in_array($val, $position_temp)){
                        $f = array_keys($function_temp, $key);
                        if(count($f)>1){
                            echo '相同的部门名称： '.$key;
                            die;
                        }
                        $p = array_keys($position_temp, $val);
                        if(count($p)>1){
                            echo '相同的职位类型名称： '.$val;
                            die;
                        }
                        $sql .= '('.$f[0].','.$p[0].','.$time.'),';
                    }else{
                        echo '不存在的职位类型： '.$val;
                        die;
                    }
                }
                else{
                    echo '不存在的部门名称： '.$key;
                    die;
                }
        }
        $sql = substr($sql, 0, -1);

        $re = $this->execute($sql,array(),$this->write_db);
        if($re){
            $this->log('Insert function_position successfully!');
        }else{
            $this->log('Insert function_position failed!!!!!!!!!!!!!!!!!!!!');
        }
    }
    //插入职位数据
    private function insert_positon(){
        $file = dirname(__FILE__).'/data/position.csv';
        if (!file_exists($file)) {
            $this->log($file."不存在,请输入正确的问题文件");exit;
        }
        //数据量小，一口气读入所有职位
        $this->file = fopen($file,"r");
        $a_info = array();
        while($temp = fgetcsv($this->file)){
            $temp = trim($temp[0]);
            if(in_array($temp, $a_info)){
                $this->log("存在重复的岗位类型:'$temp'");exit;
            }
            $a_info[] = $temp;
        }
        //一次性插入数据
        if($a_info){
            $sql = 'INSERT INTO position (`position_name`,`created`) VALUES ';
            $vals = '';
            $time = time();
            foreach($a_info as $row){
                $vals .= "('$row', $time),";
            }
            $sql = substr($sql.$vals, 0, -1);
        }else{
            $this->log("存在重复的岗位类型:'$temp'");exit;
        }
        $re = $this->execute($sql,array(),$this->write_db);
        if($re){
            $this->log('Insert position successfully!');
        }else{
            $this->log('Insert position failed!!!!!!!!!!!!!!!!!!!!');
        }
    }
    //获取职位类型
    private function _get_position_list(){
        $sql = 'SELECT position_id, position_name FROM position WHERE is_deleted = 1';
        $position = $this->get_db_rows($sql,$this->write_db,array());

        $position_temp = array();//构造职位类型KEY=》NAME数组
        foreach ($position as $row){
            $position_temp[$row['position_id']] = $row['position_name'];
        }
        return $position_temp;
    }
    //获取城市列表
    private function _get_city_list(){

        $city = Const_BaseInfo::get_city_list();
        $city_temp = array();//构造职能KEY=》NAME数组
        foreach ($city as $key => $row){
            $city_temp[$key] = $row['city_name'];
        }
        return $city_temp;
    }
    //获取职能列表
    private function _get_function_list(){
        $function = Const_BaseInfo::get_function_list();
        $function_temp = array();//构造职能KEY=》NAME数组
        foreach ($function as $key => $row){
            $function_temp[$key] = $row['function_name'];
        }
        return $function_temp;
    }
    //获取事业部列表
    private function _get_business_list(){
        $business = Const_BaseInfo::get_business_list();
        $business_temp = array();//构造职能KEY=》NAME数组
        foreach ($business as $key => $row){
            $business_temp[$key] = $row['bu_name'];
        }
        return $business_temp;
    }
    //从配置文件导入基础信息到数据库
    private function _insert_base_info(){
        //插入城市信息
        $city = Const_BaseInfo::get_city_list();
        $sql = 'INSERT INTO city (city_id,city_name,pinyin,short_pinyin,code_prefix) VALUES ';
        foreach($city as $row){
            $sql .= "({$row['city_id']},'{$row['city_name']}','{$row['pinyin']}','{$row['short_pinyin']}','{$row['code_prefix']}'),";
        }
        $sql = substr($sql, 0, -1);
        $this->execute($sql,array(),$this->write_db);
        //插入事业部信息
        $business = Const_BaseInfo::get_business_list();
        $sql = 'INSERT INTO business_unit (bu_id,bu_name) VALUES ';
        foreach($business as $row){
            $sql .= "({$row['bu_id']},'{$row['bu_name']}'),";
        }
        $sql = substr($sql, 0, -1);
        $this->execute($sql,array(),$this->write_db);

        //插入职能部门
        $function = Const_BaseInfo::get_function_list();
        $sql = 'INSERT INTO function (function_id,function_name) VALUES ';
        foreach($function as $row){
            $sql .= "({$row['function_id']},'{$row['function_name']}'),";
        }
        $sql = substr($sql, 0, -1);
        $this->execute($sql,array(),$this->write_db);

    }
    private function __destruct(){
        fclose($this->file);
        $this->i_end_job_time = microtime(true);
        echo "\n This process exhaust time totally about ";
        printf("%f", $this->i_end_job_time - $this->i_start_job_time);
        $this->log('job work end');
        return true;
    }

}